<?php
function getLibraryItemsForAutocompleter() {
	global $dbname, $db, $db_selected;
	$entry = fieldToDB($_POST['query']);
	$start = $_POST['start'];
	$limit = $_POST['limit'];
	$renderType='json';
			
	/*
	 * Get number of total records
	 */
	$sql = "SELECT LibraryID
			FROM cdc_LibraryMediaList 
			WHERE 
				Title LIKE '%$entry%' AND
                    Deleted = 0
			";
	$result = mysql_db_query($dbname,$sql);
	$totalRows = mysql_num_rows($result);
	
	$sql = "SELECT LibraryID,Title
			FROM cdc_LibraryMediaList 
			WHERE 
               Title LIKE '%$entry%' AND
               Deleted = 0
			ORDER BY Title ASC
			LIMIT $start,$limit";
	$result = mysql_db_query($dbname,$sql);
		
	$returnString = '<ul>';
	$i=0;
	
	$stringJSON   = '({"Total":"'.$totalRows.'","Library":[';
	while ($value = mysql_fetch_assoc($result)) {
		if ($renderType == 'list') {
			$libraryID = $value['LibraryID'];
			$title     = $value['Title'];
					
			if (($i % 2) > 0) { $class = 'blueRow'; }
			else { $class = ''; }	
			$returnString .= '<li class="'.$class.'" onClick="contactSaveReferralInfoObj.saveLibraryItems('.$libraryID.');"><p>'.$title.'</p></li>';
		} 
		$stringJSON .= Zend_Json::encode($value).',';
		$i++;
	}
	if ($renderType == 'list') {
		$returnString .= '</ul>';
	} elseif ($renderType == 'json') {
		$stringJSON = substr($stringJSON,0,-1);
		$stringJSON .= ']})';
		$returnString = $stringJSON;
	}
	
	echo $returnString;
}

function getLibraryItems($itemID,$itemType) {
	global $dbname, $db, $db_selected;

	if ($itemType == 'contact') {
		$sql = "SELECT L.LibraryID,L.Title,L.Abstract
				FROM cdc_LibraryMediaList L
				LEFT JOIN cdc_linkContactLibrary CL ON CL.LibraryID = L.LibraryID
				WHERE 
				CL.ContactID = '$itemID'";
	}
	$result = mysql_db_query($dbname,$sql);
	
	if (mysql_num_rows($result)>0) {
		$stringJSON   = '({"LibraryItems":[';
		while ($value = mysql_fetch_assoc($result)) {
			$stringJSON .= Zend_Json::encode($value).',';		
		}
		$stringJSON = substr($stringJSON,0,-1);
		$stringJSON .= ']})';		
	} else {
		$stringJSON = 0;
	}	
	return $stringJSON;
}

function getLibraryItem($libraryID,$purpose='view') {
	global $dbname, $db, $db_selected;
	
	if (empty($purpose)) {
		$purpose = 'view';
	}
	
	$sql = "SELECT 
					L.LibraryID,L.ISBN,L.Authors,L.Title,L.CatID,L.Abstract,
					L.LanguageEnglish,L.LanguageSpanish,L.LanguageRussian,
					L.Tags,L.Publisher,L.InhouseViewingOnly,
					C.CatTitle AS Format
				FROM cdc_LibraryMediaList L
				LEFT JOIN cdc_sysCategory C ON L.FormatID = C.CatID
				WHERE LibraryID = '$libraryID'
				ORDER BY Title";
	$result = mysql_db_query($dbname,$sql);
	$row = mysql_fetch_assoc($result);
	
	$sql2 = "SELECT CatTitle from cdc_sysCategory WHERE CatID = '".$row['CatID']."'";
	$result2 = mysql_db_query($dbname,$sql2);
	$row2 = mysql_fetch_assoc($result2);
	$row['Category'] = $row2['CatTitle'];
	
	$itemISBN = $row['ISBN'];
	$amazonRequest  = AMAZON_BASE_URI.'&SubscriptionId='.AMAZON_SUBSCRIPTION_ID.'&AWSAccessKeyId='.AMAZON_API_KEY;
	$amazonRequest .= '&Operation=ItemSearch&SearchIndex=Books&Keywords='.$itemISBN.'&ResponseGroup=Images,ItemAttributes,Small';
	$response  = file_get_contents($amazonRequest);
	$amazonXML = simplexml_load_string($response);
	
	$row['linkAmazon']  = $amazonXML->Items->Item->DetailPageURL;
	$row['imageAmazon'] = $amazonXML->Items->Item->MediumImage->URL;
	
	if ($purpose == 'view') {
		$row['Tags'] = renderTagsForItem('library',$row['LibraryID']);
	} else {
		/*
		 * Insert commas between tag words
		 */
		$row['Tags'] = str_replace(' ',', ',$row['Tags']);
	}	
	return Zend_Json::encode($row);	
}

function removeLibraryReferralItem($libraryID=null,$contactID=null) {
	global $dbname, $db, $db_selected;
	
	$sql = "DELETE from cdc_linkContactLibrary 
			WHERE 
			ContactID = '$contactID' AND
			LibraryID = '$libraryID'";
	$result = mysql_db_query($dbname,$sql);
	return $libraryID;
}

function getLibraryItemsForGrid($start=null,$limit=null,$sort=null,$acdc=null,$paging=null,$searchString=null,$renderTo=null) {
	global $dbname, $db, $db_selected;
	$returnString = 0;
	
	if ( empty($start) && empty($limit) ) {
		$start = $_POST['start'];
		$limit = $_POST['limit'];
	}
	$limitSQL = ' LIMIT '.$start.','.$limit;
	
	$_SESSION['libraryLogStart'] = $start;
		
	if (empty($sort)) {
		$sort = 'Title';
	}
	if (empty($acdc)) {
		$acdc = 'DESC';
	}
	$origAcDc = $acdc;
	
	if (!empty($searchString)) {
		/*
		 * Get searchString values
		 */
		$searchStringArray = explode('|',$searchString);
		$searchTerm = $searchStringArray[0];
		$isbn       = $searchStringArray[1];
		$category   = $searchStringArray[2];
		$whereString = '';
		
		if (!empty($searchTerm)) {
			$selectString = "
				,MATCH (Authors,Title,Abstract,Publisher,Tags) AGAINST
				('\"$searchTerm\"') AS searchScore";
			$whereString .= " ( MATCH (Authors,Title,Abstract,Publisher,Tags) AGAINST ('\"$searchTerm\"' IN BOOLEAN MODE) ) AND";
			$whereStringAlt = "	(( MATCH (Authors,Title,Abstract,Publisher,Tags) AGAINST ('$searchTerm' IN BOOLEAN MODE) ) OR 
							
									( 
										Authors   LIKE '%$searchTerm%' OR 
										Title     LIKE '%$searchTerm%' OR 
										Abstract  LIKE '%$searchTerm%' OR
										Publisher LIKE '%$searchTerm%'
									)) AND";
		}
		if (!empty($isbn)) {
			$whereString .= " ISBN = '$isbn' AND";
			$whereStringAlt .= " ISBN = '$isbn' AND";
		}
		if (!empty( $category) && ($category != 'Select...') ) {
			$whereString .= " CatID = '$category'";
			$whereStringAlt .= " CatID = '$category'";
		}
		if (substr($whereString,-3) == 'AND') {
			$whereString = substr($whereString,0,-3);
			$whereStringAlt = substr($whereStringAlt,0,-3);
		}
		if (!empty($whereString)) {
			$whereString = 'WHERE '.$whereString;
			$whereStringAlt = 'WHERE '.$whereStringAlt;
		}
		
		if (!empty($whereString)) {		
			$sql = "SELECT 
						LibraryID,
						Authors,
						Title,
						Abstract
						$selectString
						FROM cdc_LibraryMediaList 
							$whereString 
						ORDER BY 
							$sort $acdc";
			$result       = mysql_db_query($dbname,$sql);
			$totalRows    = mysql_num_rows($result);

			if ($totalRows<1) {							
				$sql = "SELECT 
							LibraryID,
							Authors,
							Title,
							Abstract
							$selectString
							FROM cdc_LibraryMediaList 
							$whereStringAlt
							ORDER BY 
							$sort $acdc";		
				$result       = mysql_db_query($dbname,$sql);
				$totalRows    = mysql_num_rows($result);
			}			
			$sqlLtd = $sql.$limitSQL;			
			$resultLtd    = mysql_db_query($dbname,$sqlLtd);				
			$totalRowsLtd = mysql_num_rows($resultLtd);
			
			$totalPages = ceil($totalRows/$limit);
			if ($paging != 1) {
				/*
				 * Switch AcDc
				 */
				if ($acdc == 'DESC') {
					$acdc = 'ASC';
				} else {
					$acdc = 'DESC';
				}
			}	
			
			if ($totalRows>0) {
				$startTitle  = $start+1;
				$limitTitle  = $start+$totalRowsLtd;
				$currentPage = ceil($limitTitle/20); 
				$resultsTitle = 'Viewing '.$startTitle.'-'.$limitTitle.' of '.$totalRows.' items';
				$stringJSON   = '({"TotalRows":'.$totalRows.',"RenderTo":"'.$renderTo.'","TotalPages":'.$totalPages.',"ResultsTitle":"'.$resultsTitle.'","Start":'.$start.',"Limit":'.$limit.',"Sort":"'.$sort.'","AcDc":"'.$acdc.'","OrigAcDc":"'.$origAcDc.'","CurrentPage":"'.$currentPage.'","SearchString":"'.$searchString.'","Items":[';
				
				
				$i=0;
				while ($value = mysql_fetch_assoc($resultLtd)) {
					$stringJSON .= Zend_Json::encode($value).',';				
					$i++;
				}
				$stringJSON = substr($stringJSON,0,-1);
				$stringJSON .= ']})';
				$returnString = $stringJSON;
			}	
		}
	}
	return $returnString;
}

function saveLibraryItem($phpJSON) {
	global $dbname, $db, $db_selected;
	
	// Decode JSON
     $phpJSON = urldecode(stripslashes($phpJSON));
	$phpArray = Zend_Json::decode($phpJSON);
	
	$formAction = $phpArray['action'];
	$libraryID  = $phpArray['libraryID'];
	$catID      = $phpArray['catID'];
	$isbn       = fieldToDB($phpArray['isbn']);
	$title      = fieldToDB($phpArray['title']);
	$authors    = fieldToDB($phpArray['authors']);
	$abstract   = fieldToDBWithHTML($phpArray['lAbstract']);
	$publisher  = fieldToDB($phpArray['publishers']);
	$formatID   = $phpArray['format'];
	$tags       = fieldToDB($phpArray['tags']);
	$inHouse    = $phpArray['inHouse'];
	$langE      = $phpArray['langEnglish'];
	$langS      = $phpArray['langSpanish'];
	$langR      = $phpArray['langRussian'];	
	
	/*
	 * Format tag string
	 */
	$tags = str_replace(',',' ',$tags);
	
	if ($formAction == 'add') {
		$sql = "INSERT into cdc_LibraryMediaList (
				ISBN,
				Authors,
				Title,
				CatID,
				Abstract,
				LanguageEnglish,
				LanguageSpanish,
				LanguageRussian,
				FormatID,
				Tags,
				Publisher,
				InhouseViewingOnly,
				DateUpdate,
				Userid,
				Active
				) values (
				'$isbn',
				'$authors',
				'$title',
				'$catID',
				'$abstract',
				'$langE',
				'$langS',
				'$langR',
				'$formatID',
				'$tags',
				'$publisher',
				'$inHouse',
				'".date('Y-m-d')."',
				'".$_SESSION['userid']."',
				1
				)";		
	} elseif ($formAction == 'edit') {
		$sql = "UPDATE cdc_LibraryMediaList SET
					ISBN               = '$isbn',
					Authors            = '$authors',
					Title              = '$title',
					CatID              = '$catID',
					Abstract           = '$abstract',
					LanguageEnglish    = '$langE',
					LanguageSpanish    = '$langS',
					LanguageRussian    = '$langR',
					FormatID           = '$formatID',
					Tags               = '$tags',
					Publisher          = '$publisher',
					InhouseViewingOnly = '$inHouse',
					DateUpdate         = '".date('Y-m-d')."',
					Userid             = '".$_SESSION['userid']."'
				WHERE
					LibraryID = '$libraryID'";
	}
	$result = mysql_db_query($dbname,$sql);
	
	/*
	 * Save any new tags
	 */
	saveNewTags('Library',$tags);
	if ($formAction == 'add') {
		$libraryID = mysql_insert_id();	
		return getLibraryItem($libraryID);
	}
}

function getLibraryItemsOut($start=null,$limit=null,$sort=null,$acdc=null,$paging=null,$memberID=null) {
	global $dbname, $db, $db_selected;
	
	if (empty($sort)) {
		$sort = 'CO.DateLent';
	}
	if (empty($acdc)) {
		$acdc = 'DESC';
	}
	$origAcDc = $acdc;
	
	if ($memberID>0) {
		$where = "CO.MemberID = '$memberID'";
		$memberArray = getMemberInfo($memberID);
		
		$memberName = $memberArray['FirstName'].' '.$memberArray['LastName'];
		$resultsTitle = 'Items checked out by: '.$memberName;
	} else {
		$where = "CO.NumberReturned = 0";
		$resultsTitle = 'Library items currently checked out';
	}
	
	$sql = "SELECT 
				L.LibraryID,L.Title,L.Authors,
				M.MemberID,M.FirstName,M.LastName,M.DoNotLend,
				CO.CheckedOutID,CO.NumberLent,CO.DateLent,CO.NumberReturned,CO.DateReturned
			FROM
				cdc_LibraryCheckOut CO
			LEFT JOIN
				cdc_LibraryMediaList L ON L.LibraryID = CO.LibraryID
			LEFT JOIN
				cdc_ContactClientDemographics M ON M.MemberID = CO.MemberID
			WHERE
				$where
			ORDER BY 
				$sort $acdc";
	$result = mysql_db_query($dbname,$sql);
	$totalRows = mysql_num_rows($result);
	
	if ($acdc == 'DESC') {
		$acdc = 'ASC';
	} else {
		$acdc = 'DESC';
	}
	
	if ($totalRows>0) {
		$startTitle  = $start+1;
		$limitTitle  = $start+$totalRowsLtd;
		$currentPage = ceil($limitTitle/20); 
		
		$stringJSON   = '({"TotalRows":'.$totalRows.',"ResultsTitle":"'.$resultsTitle.'","Sort":"'.$sort.'","AcDc":"'.$acdc.'","OrigAcDc":"'.$origAcDc.'","Items":[';
				
		$i=0;
		while ($value = mysql_fetch_assoc($result)) {
			$value['Title'] = '<a href="#" onClick="getLibraryDataObj.getLibraryItem(\'window\','.$value['LibraryID'].'); return false;" ext:qtip="<b>'.$value['Title'].'</b><br /><i>'.$value['Authors'].'</i>">'.$value['Title'].'</a>'; 
			
			$statusArray = getStatusInfo($value['CheckedOutID'],'library');
			$value['Status'] = '<div ext:qtip="'.$statusArray[1].'" class="'.$statusArray[0].'"></div>';
						
			$dateLent = new Zend_Date($value['DateLent'], Zend_Date::ISO_8601, 'en_US');
			$newDateLent = $dateLent->toString('MMM d, Y');
			
			if ( !empty($dateReturned) && ($dateReturned != '0000-00-00') ) {
				$dateReturned = new Zend_Date($value['DateReturned'], Zend_Date::ISO_8601, 'en_US');
				$newDateReturned = $dateReturned->toString('MMM d, Y');
			} else {
				$newDateReturned = '';
			}
			
			$value['DateLent'] = $newDateLent;
			$value['DateReturned'] = $newDateReturned; 
			
			$value['BorrowerName'] = $value['FirstName'].' '.$value['LastName'];			
			$stringJSON .= Zend_Json::encode($value).',';				
			$i++;
		}
		$stringJSON = substr($stringJSON,0,-1);
		$stringJSON .= ']})';
		$returnString = $stringJSON;
	} else {
		$returnString = 0;
	}
	return $returnString;
}

function deleteLibraryItem($libraryID) {
	global $dbname, $db, $db_selected;
	
	$sql = "DELETE from cdc_LibraryMediaList WHERE LibraryID = '$libraryID'";
	$result = mysql_db_query($dbname,$sql);
}
?>